const db=require('../dbConfig');

// ---------------------不需要登录的---------------------

// 探索(获取所有用户不是私密的图片)
let getExplore=(page)=>{
  	page = (page - 1) *20;
   	let sql=`select image.*,FROM_UNIXTIME(UNIX_TIMESTAMP(image.create_time), '%Y-%m-%d %H:%i:%s') create_time,user.name,user.avator from image,user where image.user_id=user.id and is_private=0`;
	sql+=` order by  image.create_time Desc LIMIT ?,20`;
	return db(sql,[page]);    
}

// 探索(获取所有用户不是私密的图片 数量)
let getExploreCount=(page)=>{
	let sql=`select count(*) count from image where is_private=0`;
	return db(sql);    
}

// 上传图片
let uploadImg=async(obj,userId)=>{
	let {imgId,imgUrl}=obj;
 	let sql=`insert into image (id,user_id,url) values(?,?,?)`;
	return await db(sql,[imgId,userId,imgUrl]);
}  

// ---------------------需要登录的---------------------
// 获取我的图片列表(包括搜索)和数量
let getList=async(obj,userId)=>{
	let{imgNote,isPrivate,page}=obj;
	let data={};
	let sql=`select image.id,url,user_id,note,is_private,user.name,user.avator,
	FROM_UNIXTIME(UNIX_TIMESTAMP(image.create_time), '%Y-%m-%d %H:%i:%s') create_time from image,user where image.user_id=user.id `;
	let arr=[userId];
	sql+=`and image.user_id=?`;
	sql+=`and is_private=? and  note like ? `; 
	if(page){
		page = (page - 1) *10;
		sql+=` order by  image.create_time Desc LIMIT ?,10`;
	}
	arr.push(isPrivate);arr.push('%'+imgNote+'%');arr.push(page);
	let result=await db(sql,arr);
	if(result.length==0){
		return {
		 	result:[],
		 	count:0
		}
	}
	else{
		data.result=result;
		let sql2='select count(*) count from image where',arr2=[userId];
		sql2+=` user_id=? and `;
		sql2+=` is_private=? and  note like ? `; 
		arr2.push(isPrivate);
		arr2.push('%'+imgNote+'%'); 
		data.count= await db(sql2,arr2);
		data.count=data.count[0].count;
		return data;
	}
}

// 获取自己的图片总数(包括私密图片数量)
let getCount=async(userId)=>{
	let result={};
	let sql=`select count(*) count from image where user_id=?`;
	result.imgCount = await db(sql,[userId]);
	let sql2=`select count(*) count from image where user_id=? and is_private=1`;
	result.privateImgCount = await db(sql2,[userId]);
	result.imgCount=result.imgCount[0].count;
  	result.privateImgCount=result.privateImgCount[0].count;
	return result;
}

// 删除自己上传的图片（批量删除）
let byDelete=(obj,userId)=>{
	let {imgId} = obj;
	imgId=imgId.join(',');
	let sql=`delete from image where FIND_IN_SET(id,?) and user_id=?`;
	return db(sql,[imgId,userId]);
}

// 修改自己的图片数据
let byUpdate=(obj,userId)=>{
	let {isPrivate,imgNote,imgId}=obj;
	let sql;
	let arr=[];
	if(isPrivate){
		sql=`update image set is_private=? where id=? and user_id=?`; 
		arr.push(isPrivate);
	}
	else{
		sql=`update image set note=? where id=? and user_id=?`;
		arr.push(imgNote);
	}
	arr.push(imgId);
	arr.push(userId);
	return db(sql,arr);
}

 
// ---------------------管理员---------------------
// 获取所有用户上传的图片(包括搜索)和数量
let _select=async(obj)=>{
	let{imgNote,isPrivate,page}=obj;
	let data={};
	let sql=`select image.id,url,user_id,note,is_private,user.name,user.avator,
	FROM_UNIXTIME(UNIX_TIMESTAMP(image.create_time), '%Y-%m-%d %H:%i:%s') create_time from image,user where image.user_id=user.id `;
	let arr=[];
	sql+=` and is_private=? and  note like ? `; 
	if(page){
		page = (page - 1) *10;
		sql+=` order by  image.create_time Desc LIMIT ?,10`;
	}
	arr.push(isPrivate);arr.push('%'+imgNote+'%');arr.push(page);
	let result=await db(sql,arr);
	if(result.length==0){
		return {
		 	result:[],
		 	count:0
		}
	}
	else{
		data.result=result;
		let sql2='select count(*) count from image where',arr2=[];
		sql2+=` is_private=? and  note like ? `; 
		arr2.push(isPrivate);
		arr2.push('%'+imgNote+'%'); 
		data.count= await db(sql2,arr2);
		data.count=data.count[0].count;
		return data;
	}
}

// 获取所有用户的图片总数(包括私密图片数量)
let count=async()=>{
	let result={};
  	let sql=`select count(*) count from image`;
  	result.imgCount = await db(sql);
  	let sql2=`select count(*) count from image where is_private=1`;
  	result.privateImgCount= await db(sql2);
  	result.imgCount=result.imgCount[0].count;
  	result.privateImgCount=result.privateImgCount[0].count;
  	return result;
}

// 根据图片id修改图片数据
let _update=(obj)=>{
	let {isPrivate,imgNote,imgId}=obj;
	let sql;
	let arr=[];
	if(isPrivate){
		sql=`update image set is_private=? where id=?`; 
		arr.push(isPrivate);
	}
	else{
		sql=`update image set note=? where id=?`;
		arr.push(imgNote);
	}
	arr.push(imgId);
	return db(sql,arr);
} 

 // 根据图片id删除已上传的图片
let _delete=(id)=>{
	id=id.join(',');
	let sql=`delete from image where FIND_IN_SET(id,?)`;
	return db(sql,[id]);
}

const serviceImage={
	// noLogin
	getExplore,getExploreCount,
	// Login
	getList,getCount,byUpdate,byDelete,uploadImg,
	// admin
	_select,count,_update,_delete
}

module.exports=serviceImage